--1 REGION
SELECT
	REGION,
	PRODUCT, 
	FEATURE, 
	SUM(MTD_CNT) MTD_CNT, 
	SUM(PREV_MTD_CNT) PREV_MTD_CNT, 
	SUM(PREV_MON_CNT) PREV_MON_CNT, 
    SUM(MTD_CNT)/AVG(LAST_DAY/DOM) PACING,
    CASE WHEN SUM(PREV_MON_CNT)=0 THEN AVG(0) ELSE CAST(SUM(MTD_CNT)/AVG(LAST_DAY/DOM)  AS DECIMAL(20,6))/SUM(PREV_MON_CNT) END PACE_RATE,
	'REGION' ROLLUP_TYPE
FROM
(
SELECT 
	D.REGION,
	D.TRAC_ID,
	D.PRODUCT, 
	D.FEATURE, 
	COALESCE(E.CNT, 0) MTD_CNT, 
	COALESCE(J.CNT, 0) PREV_MTD_CNT, 
	COALESCE(H.CNT, 0) PREV_MON_CNT, 
	G.LAST_DAY,
	F.DOM,
	'REGION' ROLLUP_TYPE	
FROM 
(	
	SELECT P.PRODUCT, P.FEATURE, TRAC_ID, REGION 
	FROM MIS.ACC_RAW A 
	RIGHT OUTER JOIN
	(	
		SELECT DISTINCT product, feature  FROM MIS.ACC_CHANNEL
	)	P
	ON P.PRODUCT = A.PRODUCT AND A.FEATURE = P.FEATURE 
	GROUP BY 1,2,3 ,4
) 	D

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
	FROM 
		MIS.ACC_RAW A,

		(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE  ) C
	WHERE A.START_DT BETWEEN C.BOM AND C.EOM  
	 GROUP BY 1, 2, 3
)  E 
ON D.PRODUCT=E.PRODUCT AND D.FEATURE=E.FEATURE AND D.TRAC_ID=E.TRAC_ID

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  	FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)		) C
  	WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  	GROUP BY 1,2,3
)  J 
ON D.PRODUCT=J.PRODUCT AND D.FEATURE=J.FEATURE AND D.TRAC_ID=J.TRAC_ID

LEFT OUTER JOIN 
 	(	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  		FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	) C
  		WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  		GROUP BY 1,2,3
  	) H 
ON D.PRODUCT=H.PRODUCT AND D.FEATURE=H.FEATURE AND D.TRAC_ID=H.TRAC_ID ,

(	
 	SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	
) 	F,

(	
 	SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR 	WHERE MONTH_OF_CALENDAR=
 	(	SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)	
) 	G
) SUBS
GROUP BY 1,2,3,9
UNION
SELECT
	'' REGION, 
	'' PRODUCT, 
	'' FEATURE, 
	0 MTD_CNT, 
	0 PREV_MTD_CNT, 
	0 PREV_MON_CNT, 
	1 PACING,
	0 PACE_RATE,
	'REGION'	 ROLLUP_TYPE
FROM 
	MIS.ACC_RAW 
ORDER BY 1,2,3
;

--2 STATE_CD
SELECT
	STATE_CD,
	PRODUCT, 
	FEATURE, 
	SUM(MTD_CNT) MTD_CNT, 
	SUM(PREV_MTD_CNT) PREV_MTD_CNT, 
	SUM(PREV_MON_CNT) PREV_MON_CNT, 
    SUM(MTD_CNT)/AVG(LAST_DAY/DOM) PACING,
    CASE WHEN SUM(PREV_MON_CNT)=0 THEN AVG(0) ELSE CAST(SUM(MTD_CNT)/AVG(LAST_DAY/DOM)  AS DECIMAL(20,6))/SUM(PREV_MON_CNT) END PACE_RATE,
	'STATE_CD' ROLLUP_TYPE
FROM
(
SELECT 
	STATE_CD,
	D.TRAC_ID,
	D.PRODUCT, 
	D.FEATURE, 
	COALESCE(E.CNT, 0) MTD_CNT, 
	COALESCE(J.CNT, 0) PREV_MTD_CNT, 
	COALESCE(H.CNT, 0) PREV_MON_CNT, 
	G.LAST_DAY,
	F.DOM,
	'STATE_CD' ROLLUP_TYPE	
FROM 
(	
	SELECT P.PRODUCT, P.FEATURE, TRAC_ID, SUBSTR(TRAC_ID,1,2)  STATE_CD 
	FROM MIS.ACC_RAW A 
	RIGHT OUTER JOIN
	(	
		SELECT DISTINCT product, feature  FROM MIS.ACC_CHANNEL
	)	P
	ON P.PRODUCT = A.PRODUCT AND A.FEATURE = P.FEATURE 
	GROUP BY 1,2,3 ,4
) 	D

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
	FROM 
		MIS.ACC_RAW A,

		(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE  ) C
	WHERE A.START_DT BETWEEN C.BOM AND C.EOM  
	 GROUP BY 1, 2, 3
)  E 
ON D.PRODUCT=E.PRODUCT AND D.FEATURE=E.FEATURE AND D.TRAC_ID=E.TRAC_ID

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  	FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)		) C
  	WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  	GROUP BY 1,2,3
)  J 
ON D.PRODUCT=J.PRODUCT AND D.FEATURE=J.FEATURE AND D.TRAC_ID=J.TRAC_ID

LEFT OUTER JOIN 
 	(	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  		FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	) C
  		WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  		GROUP BY 1,2,3
  	) H 
ON D.PRODUCT=H.PRODUCT AND D.FEATURE=H.FEATURE AND D.TRAC_ID=H.TRAC_ID ,

(	
 	SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	
) 	F,

(	
 	SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR 	WHERE MONTH_OF_CALENDAR=
 	(	SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)	
) 	G
) SUBS
GROUP BY 1,2,3,9
UNION
SELECT
	'' STATE_CD, 
	'' PRODUCT, 
	'' FEATURE, 
	0 MTD_CNT, 
	0 PREV_MTD_CNT, 
	0 PREV_MON_CNT, 
	1 PACING,
	0 PACE_RATE,
	'STATE_CD'	 ROLLUP_TYPE
FROM 
	MIS.ACC_RAW 
ORDER BY 1,2,3
;
--3 MARKET
SELECT
	MARKET,
	PRODUCT, 
	FEATURE, 
	SUM(MTD_CNT) MTD_CNT, 
	SUM(PREV_MTD_CNT) PREV_MTD_CNT, 
	SUM(PREV_MON_CNT) PREV_MON_CNT, 
    SUM(MTD_CNT)/AVG(LAST_DAY/DOM) PACING,
    CASE WHEN SUM(PREV_MON_CNT)=0 THEN AVG(0) ELSE CAST(SUM(MTD_CNT)/AVG(LAST_DAY/DOM)  AS DECIMAL(20,6))/SUM(PREV_MON_CNT) END PACE_RATE,
	'MARKET' ROLLUP_TYPE
FROM
(
SELECT 
	D.MARKET,
	D.TRAC_ID,
	D.PRODUCT, 
	D.FEATURE, 
	COALESCE(E.CNT, 0) MTD_CNT, 
	COALESCE(J.CNT, 0) PREV_MTD_CNT, 
	COALESCE(H.CNT, 0) PREV_MON_CNT, 
	G.LAST_DAY,
	F.DOM,
	'MARKET' ROLLUP_TYPE	
FROM 
(	
	SELECT P.PRODUCT, P.FEATURE, TRAC_ID, A.MARKET 
	FROM MIS.ACC_RAW A 
	RIGHT OUTER JOIN
	(	
		SELECT DISTINCT product, feature  FROM MIS.ACC_CHANNEL
	)	P
	ON P.PRODUCT = A.PRODUCT AND A.FEATURE = P.FEATURE 
	GROUP BY 1,2,3 ,4
) 	D

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
	FROM 
		MIS.ACC_RAW A,

		(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE  ) C
	WHERE A.START_DT BETWEEN C.BOM AND C.EOM  
	 GROUP BY 1, 2, 3
)  E 
ON D.PRODUCT=E.PRODUCT AND D.FEATURE=E.FEATURE AND D.TRAC_ID=E.TRAC_ID

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  	FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)		) C
  	WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  	GROUP BY 1,2,3
)  J 
ON D.PRODUCT=J.PRODUCT AND D.FEATURE=J.FEATURE AND D.TRAC_ID=J.TRAC_ID

LEFT OUTER JOIN 
 	(	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  		FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	) C
  		WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  		GROUP BY 1,2,3
  	) H 
ON D.PRODUCT=H.PRODUCT AND D.FEATURE=H.FEATURE AND D.TRAC_ID=H.TRAC_ID ,

(	
 	SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	
) 	F,

(	
 	SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR 	WHERE MONTH_OF_CALENDAR=
 	(	SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)	
) 	G
) SUBS
GROUP BY 1,2,3,9
UNION
SELECT
	'' MARKET, 
	'' PRODUCT, 
	'' FEATURE, 
	0 MTD_CNT, 
	0 PREV_MTD_CNT, 
	0 PREV_MON_CNT, 
	1 PACING,
	0 PACE_RATE,
	'MARKET'	 ROLLUP_TYPE
FROM 
	MIS.ACC_RAW 
ORDER BY 1,2,3
;
--4 AREA_MGR
SELECT
	AREA_MGR,
	PRODUCT, 
	FEATURE, 
	SUM(MTD_CNT) MTD_CNT, 
	SUM(PREV_MTD_CNT) PREV_MTD_CNT, 
	SUM(PREV_MON_CNT) PREV_MON_CNT, 
    SUM(MTD_CNT)/AVG(LAST_DAY/DOM) PACING,
    CASE WHEN SUM(PREV_MON_CNT)=0 THEN AVG(0) ELSE CAST(SUM(MTD_CNT)/AVG(LAST_DAY/DOM)  AS DECIMAL(20,6))/SUM(PREV_MON_CNT) END PACE_RATE,
	'AREA_MGR' ROLLUP_TYPE
FROM
(
SELECT 
	D.AREA_MGR,
	D.TRAC_ID,
	D.PRODUCT, 
	D.FEATURE, 
	COALESCE(E.CNT, 0) MTD_CNT, 
	COALESCE(J.CNT, 0) PREV_MTD_CNT, 
	COALESCE(H.CNT, 0) PREV_MON_CNT, 
	G.LAST_DAY,
	F.DOM,
	'AREA_MGR' ROLLUP_TYPE	
FROM 
(	
	SELECT P.PRODUCT, P.FEATURE, A.TRAC_ID, X.AREA_MGR 
	FROM MIS.ACC_RAW A 				 
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					X 
					ON	A.TRAC_ID=X.TRAC_ID 
	RIGHT OUTER JOIN
	(	
		SELECT DISTINCT product, feature  FROM MIS.ACC_CHANNEL
	)	P
	ON P.PRODUCT = A.PRODUCT AND A.FEATURE = P.FEATURE 
	GROUP BY 1,2,3 ,4
) 	D

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
	FROM 
		MIS.ACC_RAW A,

		(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE  ) C
	WHERE A.START_DT BETWEEN C.BOM AND C.EOM  
	 GROUP BY 1, 2, 3
)  E 
ON D.PRODUCT=E.PRODUCT AND D.FEATURE=E.FEATURE AND D.TRAC_ID=E.TRAC_ID

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  	FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)		) C
  	WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  	GROUP BY 1,2,3
)  J 
ON D.PRODUCT=J.PRODUCT AND D.FEATURE=J.FEATURE AND D.TRAC_ID=J.TRAC_ID

LEFT OUTER JOIN 
 	(	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  		FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	) C
  		WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  		GROUP BY 1,2,3
  	) H 
ON D.PRODUCT=H.PRODUCT AND D.FEATURE=H.FEATURE AND D.TRAC_ID=H.TRAC_ID ,

(	
 	SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	
) 	F,

(	
 	SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR 	WHERE MONTH_OF_CALENDAR=
 	(	SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)	
) 	G
) SUBS
GROUP BY 1,2,3,9
UNION
SELECT
	'' MARKET, 
	'' PRODUCT, 
	'' FEATURE, 
	0 MTD_CNT, 
	0 PREV_MTD_CNT, 
	0 PREV_MON_CNT, 
	1 PACING,
	0 PACE_RATE,
	'AREA_MGR'	 ROLLUP_TYPE
FROM 
	MIS.ACC_RAW 
ORDER BY 1,2,3
;
--5 ACCT_MGR
SELECT
	ACCT_MGR,
	PRODUCT, 
	FEATURE, 
	SUM(MTD_CNT) MTD_CNT, 
	SUM(PREV_MTD_CNT) PREV_MTD_CNT, 
	SUM(PREV_MON_CNT) PREV_MON_CNT, 
    SUM(MTD_CNT)/AVG(LAST_DAY/DOM) PACING,
    CASE WHEN SUM(PREV_MON_CNT)=0 THEN AVG(0) ELSE CAST(SUM(MTD_CNT)/AVG(LAST_DAY/DOM)  AS DECIMAL(20,6))/SUM(PREV_MON_CNT) END PACE_RATE,
	'ACCT_MGR' ROLLUP_TYPE
FROM
(
SELECT 
	D.ACCT_MGR,
	D.TRAC_ID,
	D.PRODUCT, 
	D.FEATURE, 
	COALESCE(E.CNT, 0) MTD_CNT, 
	COALESCE(J.CNT, 0) PREV_MTD_CNT, 
	COALESCE(H.CNT, 0) PREV_MON_CNT, 
	G.LAST_DAY,
	F.DOM,
	'ACCT_MGR' ROLLUP_TYPE	
FROM 
(	
	SELECT P.PRODUCT, P.FEATURE, A.TRAC_ID, X.ACCT_MGR 
	FROM MIS.ACC_RAW A 				 
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					X 
					ON	A.TRAC_ID=X.TRAC_ID 
	RIGHT OUTER JOIN
	(	
		SELECT DISTINCT product, feature  FROM MIS.ACC_CHANNEL
	)	P
	ON P.PRODUCT = A.PRODUCT AND A.FEATURE = P.FEATURE 
	GROUP BY 1,2,3 ,4
) 	D

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
	FROM 
		MIS.ACC_RAW A,

		(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE  ) C
	WHERE A.START_DT BETWEEN C.BOM AND C.EOM  
	 GROUP BY 1, 2, 3
)  E 
ON D.PRODUCT=E.PRODUCT AND D.FEATURE=E.FEATURE AND D.TRAC_ID=E.TRAC_ID

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  	FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)		) C
  	WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  	GROUP BY 1,2,3
)  J 
ON D.PRODUCT=J.PRODUCT AND D.FEATURE=J.FEATURE AND D.TRAC_ID=J.TRAC_ID

LEFT OUTER JOIN 
 	(	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  		FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	) C
  		WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  		GROUP BY 1,2,3
  	) H 
ON D.PRODUCT=H.PRODUCT AND D.FEATURE=H.FEATURE AND D.TRAC_ID=H.TRAC_ID ,

(	
 	SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	
) 	F,

(	
 	SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR 	WHERE MONTH_OF_CALENDAR=
 	(	SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)	
) 	G
) SUBS
GROUP BY 1,2,3,9
UNION
SELECT
	'' MARKET, 
	'' PRODUCT, 
	'' FEATURE, 
	0 MTD_CNT, 
	0 PREV_MTD_CNT, 
	0 PREV_MON_CNT, 
	1 PACING,
	0 PACE_RATE,
	'ACCT_MGR'	 ROLLUP_TYPE
FROM 
	MIS.ACC_RAW 
ORDER BY 1,2,3
;
--6 PORTFOLIO
SELECT
	PORTFOLIO,
	PRODUCT, 
	FEATURE, 
	SUM(MTD_CNT) MTD_CNT, 
	SUM(PREV_MTD_CNT) PREV_MTD_CNT, 
	SUM(PREV_MON_CNT) PREV_MON_CNT, 
    SUM(MTD_CNT)/AVG(LAST_DAY/DOM) PACING,
    CASE WHEN SUM(PREV_MON_CNT)=0 THEN AVG(0) ELSE CAST(SUM(MTD_CNT)/AVG(LAST_DAY/DOM)  AS DECIMAL(20,6))/SUM(PREV_MON_CNT) END PACE_RATE,
	'PORTFOLIO' ROLLUP_TYPE
FROM
(
SELECT 
	D.PORTFOLIO,
	D.TRAC_ID,
	D.PRODUCT, 
	D.FEATURE, 
	COALESCE(E.CNT, 0) MTD_CNT, 
	COALESCE(J.CNT, 0) PREV_MTD_CNT, 
	COALESCE(H.CNT, 0) PREV_MON_CNT, 
	G.LAST_DAY,
	F.DOM,
	'PORTFOLIO' ROLLUP_TYPE	
FROM 
(	
	SELECT P.PRODUCT, P.FEATURE, A.TRAC_ID, X.PORTFOLIO 
	FROM MIS.ACC_RAW A 				 
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					X 
					ON	A.TRAC_ID=X.TRAC_ID 
	RIGHT OUTER JOIN
	(	
		SELECT DISTINCT product, feature  FROM MIS.ACC_CHANNEL
	)	P
	ON P.PRODUCT = A.PRODUCT AND A.FEATURE = P.FEATURE 
	GROUP BY 1,2,3 ,4
) 	D

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
	FROM 
		MIS.ACC_RAW A,

		(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE  ) C
	WHERE A.START_DT BETWEEN C.BOM AND C.EOM  
	 GROUP BY 1, 2, 3
)  E 
ON D.PRODUCT=E.PRODUCT AND D.FEATURE=E.FEATURE AND D.TRAC_ID=E.TRAC_ID

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  	FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)		) C
  	WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  	GROUP BY 1,2,3
)  J 
ON D.PRODUCT=J.PRODUCT AND D.FEATURE=J.FEATURE AND D.TRAC_ID=J.TRAC_ID

LEFT OUTER JOIN 
 	(	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  		FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	) C
  		WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  		GROUP BY 1,2,3
  	) H 
ON D.PRODUCT=H.PRODUCT AND D.FEATURE=H.FEATURE AND D.TRAC_ID=H.TRAC_ID ,

(	
 	SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	
) 	F,

(	
 	SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR 	WHERE MONTH_OF_CALENDAR=
 	(	SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)	
) 	G
) SUBS
GROUP BY 1,2,3,9
UNION
SELECT
	'' MARKET, 
	'' PRODUCT, 
	'' FEATURE, 
	0 MTD_CNT, 
	0 PREV_MTD_CNT, 
	0 PREV_MON_CNT, 
	1 PACING,
	0 PACE_RATE,
	'PORTFOLIO'	 ROLLUP_TYPE
FROM 
	MIS.ACC_RAW 
ORDER BY 1,2,3
;
--7 TRAC_ID
SELECT 
	D.TRAC_ID,
	D.PRODUCT, 
	D.FEATURE, 
	COALESCE(E.CNT, 0) MTD_CNT, 
	COALESCE(J.CNT, 0) PREV_MTD_CNT, 
	COALESCE(H.CNT, 0) PREV_MON_CNT, 
	MTD_CNT * G.LAST_DAY / F.DOM PACING,
	CASE WHEN PREV_MON_CNT=0 THEN 0 ELSE CAST(PACING AS DECIMAL(7,4))/PREV_MON_CNT END PACE_RATE,
	'TRAC_ID' ROLLUP_TYPE	

FROM 
(	
	SELECT P.PRODUCT, P.FEATURE, TRAC_ID 
	FROM MIS.ACC_RAW A 
	RIGHT OUTER JOIN
	(	
		SELECT DISTINCT product, feature  FROM MIS.ACC_CHANNEL
	)	P
	ON P.PRODUCT = A.PRODUCT AND A.FEATURE = P.FEATURE 
	GROUP BY 1,2,3 
) 	D

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
	FROM 
		MIS.ACC_RAW A,

		(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE  ) C
	WHERE A.START_DT BETWEEN C.BOM AND C.EOM  
	 GROUP BY 1, 2, 3
)  E 
ON D.PRODUCT=E.PRODUCT AND D.FEATURE=E.FEATURE AND D.TRAC_ID=E.TRAC_ID

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  	FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)		) C
  	WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  	GROUP BY 1,2,3
)  J 
ON D.PRODUCT=J.PRODUCT AND D.FEATURE=J.FEATURE AND D.TRAC_ID=J.TRAC_ID

LEFT OUTER JOIN 
 	(	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  		FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	) C
  		WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  		GROUP BY 1,2,3
  	) H 
ON D.PRODUCT=H.PRODUCT AND D.FEATURE=H.FEATURE AND D.TRAC_ID=H.TRAC_ID ,

(	
 	SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	
) 	F,

(	
 	SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR 	WHERE MONTH_OF_CALENDAR=
 	(	SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)	
) 	G
UNION
SELECT
	'' TRAC_ID,
	'' PRODUCT, 
	'' FEATURE, 
	0 MTD_CNT, 
	0 PREV_MTD_CNT, 
	0 PREV_MON_CNT, 
	1 PACING,
	0 PACE_RATE,
	'TRAC_ID'	 ROLLUP_TYPE
FROM 
	MIS.ACC_RAW 
ORDER BY 1,2,3
;